CREATE ROLE
CREATE ROLE — Define a new database role
Synopsis
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
Description
CREATE ROLE adds a new role to the database cluster. A role is an entity that can own database objects and have database privileges. Depending on how a role is used, it can be considered a "user", a "group", or both. To use this command, you must have CREATEROLE privilege or be a database superuser.
Note that roles are defined at the database cluster level, and therefore are available in all databases in the cluster.
Parameters
name
The name of the new role.
SUPERUSER
NOSUPERUSER
These clauses determine whether the new role is a "superuser" who can override all access restrictions within the database.
Superuser status is dangerous and should only be used when truly needed. To create a new superuser, you must be a superuser yourself. If not specified, the default is NOSUPERUSER.
CREATEDB
NOCREATEDB
These clauses define a role's ability to create databases. If CREATEDB is specified, the defined role will be allowed to create new databases. Specifying NOCREATEDB will deny the role the ability to create databases. If not specified, the default is NOCREATEDB.
CREATEROLE
NOCREATEROLE
These clauses determine whether a role is allowed to create new roles (i.e., execute CREATE ROLE). A role with CREATEROLE privilege can also modify and drop other roles. If not specified, the default is NOCREATEROLE.
INHERIT
NOINHERIT
If the new role is a member of other roles, these clauses determine whether the new role "inherits" privileges from those roles. The roles of which the new role is a member are called its parent roles. A role with the INHERIT attribute can automatically use any database privileges that have been granted to its direct or indirect parent roles. Without INHERIT, membership in another role only grants the ability to use SET ROLE to switch to that other role, and only after doing so are that role's privileges available. If not specified, the default is INHERIT.
LOGIN
NOLOGIN
These clauses determine whether a role is allowed to log in, i.e., whether the role can be given as the initial session authorization name during a client connection. A role with the LOGIN attribute can be considered a "user". A role without this attribute is useful for managing database privileges, but is not a "user" in the usual sense of the word. If not specified, the default is NOLOGIN, but when CREATE ROLE is invoked via CREATE USER, the default is LOGIN.
REPLICATION
NOREPLICATION
These clauses determine whether a role is a replication role. A role must have this attribute (or be a superuser) to connect to the server in replication mode (physical or logical replication) and to create or drop replication slots. A role with the REPLICATION attribute is a highly privileged role and should only be used for roles that genuinely need replication. If not specified, the default is NOREPLICATION. You must be a superuser to create a new role with the REPLICATION attribute.
BYPASSRLS
NOBYPASSRLS
These clauses determine whether a role can bypass every row-level security (RLS) policy. The default is NOBYPASSRLS. You must be a superuser to create a new role with the BYPASSRLS attribute.
Note that pg_dump will set row_security to OFF by default to ensure that all contents of a table are dumped. If the user running pg_dump does not have appropriate permissions, an error will be returned. However, superusers and the owner of the table being dumped can always bypass RLS.
CONNECTION LIMIT connlimit
If the role can log in, this specifies how many concurrent connections the role can establish. -1 (the default) means no limit. Note that this limit applies only to ordinary connections. Prepared transactions and background worker connections are not subject to this limit.
[ ENCRYPTED ] PASSWORD 'password'
PASSWORD NULL
Sets the role's password (passwords are only useful for roles with the LOGIN attribute, but you can define a password for a role without this attribute regardless). If you do not plan to use password authentication, you can omit this option. If no password is specified, the password will be set to null and password authentication for this user will always fail. You can also explicitly write a null password using PASSWORD NULL.
Passwords are always stored encrypted in the system catalog. The ENCRYPTED keyword has no actual effect; it exists only for backward compatibility. The encryption method is determined by the configuration parameter password_encryption. If the current password string is already in MD5-encrypted or SCRAM-encrypted format, the password string will be stored as-is regardless of the value of password_encryption (because the system cannot decrypt a password string encrypted in a different format). This approach allows encrypted passwords to be reloaded during dump/restore operations.
VALID UNTIL 'timestamp'
The VALID UNTIL mechanism sets a date and time after which the role's password will no longer be valid. If this clause is omitted, the password will always be valid.
IN ROLE role_name
The IN ROLE clause lists one or more existing roles to which the new role will be immediately added as a new member (note that there is no option to add the new role as an administrator; a separate GRANT command is required for that).
IN GROUP role_name
IN GROUP is an obsolete spelling of IN ROLE.
ROLE role_name
The ROLE clause lists one or more existing roles that will be automatically added as members of the new role (this effectively makes the new role a "group").
ADMIN role_name
The ADMIN clause is similar to ROLE, but the named roles are added to the new role with WITH ADMIN OPTION, allowing them to grant membership in this role to others.
USER role_name
The USER clause is an obsolete spelling of the ROLE clause.
SYSID uid
The SYSID clause is ignored, but it is still accepted for backward compatibility.
Notes
Use ALTER ROLE to change the attributes of a role, and DROP ROLE to remove a role. All attributes specified with CREATE ROLE can be modified by subsequent ALTER ROLE commands.
The best way to add and remove group role members is to use GRANT and REVOKE.
The VALID UNTIL clause defines an expiration time for a password only, not for the role itself. In particular, the expiration time is not enforced when logging in using a non-password-based authentication method.
The INHERIT attribute governs the inheritance of grantable privileges (i.e., access privileges to database objects and role memberships). It does not apply to the special role attributes set by CREATE ROLE and ALTER ROLE. For example, being a member of a role with CREATEDB privilege does not immediately grant the ability to create databases, even if INHERIT is set; you must first use SET ROLE to assume that role before creating a database.
Be careful with the CREATEROLE privilege. There is no concept of inheritance for CREATEROLE privileges. This means that even if a role does not have a specific privilege but is allowed to create other roles, it can easily create another role with privileges different from its own (except for creating roles with superuser privileges). For example, if the role "user" has CREATEROLE privilege but not CREATEDB privilege, it can still create a new role with CREATEDB privilege. Therefore, roles with CREATEROLE privilege should be considered quasi-superuser roles.
The system includes a program createuser that has the same functionality as CREATE ROLE (in fact, it calls this command), but can be run from the command shell.
CONNECTION LIMIT is enforced only approximately; if two new sessions are started at nearly the same time and the role has only one connection "slot" remaining, both may fail. Also, the limit is never enforced against superusers.
Care must be taken when specifying an unencrypted password with this command. The command will be transmitted to the server in plain text, and it may also be logged in the client's command history or the server log. However, the createuser command transmits encrypted passwords. Additionally, psql includes a \password command that can be used to securely change a password.
Examples
Create a role that can log in but has no password:
CREATE ROLE jonathan LOGIN;
Create a role with a password:
CREATE USER davide WITH PASSWORD 'jw8s0F4';
(CREATE USER is identical to CREATE ROLE, except that it implies LOGIN.)
Create a role whose password expires at the end of 2004. At the first second of 2005, the password becomes invalid.
CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';
Create a role that can create databases and manage roles:
CREATE ROLE admin WITH CREATEDB CREATEROLE;